import { and, eq, SQLWrapper } from "drizzle-orm";
import { db } from "./seed";
import {
  brands,
  BrandSchema,
  categories,
  CategorySchema,
  productPriceHistory,
  ProductPriceHistorySchema,
  products,
  ProductSchema,
  stores,
  StoreSchema,
} from "@supersavely/db/schema";

export enum Supermarket {
  WWS = "woolworths",
  Coles = "coles",
  ALDI = "aldi",
  IGA = "iga",
}

export const marketImages = new Map<Supermarket, string>();
marketImages.set(
  Supermarket.WWS,
  "https://cdn0.woolworths.media/content/content/icon-header-logo-only.png"
);
marketImages.set(
  Supermarket.Coles,
  "https://www.coles.com.au/content/dam/coles/global/icons/favicons/favicon.ico"
);
marketImages.set(
  Supermarket.ALDI,
  "https://corporate.aldi.us/fileadmin/_processed_/7/6/csm_aldi_logo_2017_b951eeb691.jpg"
);
marketImages.set(
  Supermarket.IGA,
  "https://igav3-metcdn-com.global.ssl.fastly.net/content/uploads/2023/12/26120030/IGA-Australia-Logo.svg"
);

export async function importProduct(market: Supermarket, data: any) {
  // data: json array, add "PAGE_LINK" to record the product link
  console.log(`Importing product from ${market}...`);
  if (market === Supermarket.WWS) {
    console.log("Importing from WWS...");
    await importProductFromWws(data);
  } else if (market === Supermarket.Coles) {
    console.log("Importing from Coles...");
    await importProductFromColes(data);
  } else {
    console.warn("Importing skip because of market error!!!");
  }
  console.log("Importing done!");
}

const WWS_KEYS = {
  PAGE_LINK: "PAGE_LINK", // Product link, added by scraper
  PRODUCT: "Product",
  P_STOCK_CODE: "Stockcode",
  P_BAR_CODE: "Barcode",
  P_CUP_PRICE: "CupPrice",
  P_IN_CUP_PRICE: "InstoreCupPrice",
  P_CUP_MEASURE: "CupMeasure",
  P_PRICE: "Price",
  P_IN_PRICE: "InstorePrice",
  P_NAME: "Name",
  P_DISP_NAME: "DisplayName",
  P_URL_NAME: "UrlFriendlyName",
  P_DESC: "Description",
  P_IMAGE: "MediumImageFile",
  P_UNIT: "Unit",
  P_DESC_FULL: "FullDescription",
  P_DESC_RICH: "RichDescription",
  P_CAT: "SapCategories",
  P_C_CAT_L1: "SapDepartmentName",
  P_C_CAT_L2: "SapCategoryName",
  P_C_CAT_L3: "SapSubCategoryName",
  P_C_CAT_L4: "SapSegmentName",
  P_BRAND: "Brand",
};

const COLES_KEYS = {
  PAGE_LINK: "PAGE_LINK", // Product link, added by scraper
  STOCK_CODE: "id",
  PRICING: "pricing",
  P_UNIT: "unit",
  P_U_CUP_PRICE: "price",
  P_U_CUP_MEASURE_QUANT: "ofMeasureQuantity",
  P_U_CUP_MEASURE_UNIT: "ofMeasureUnits",
  P_PRICE: "now",
  NAME: "name",
  DESC: "description",
  DESC_FULL: "longDescription",
  CAT: "onlineHeirs",
  C_A_CAT_L1: "subCategory",
  C_A_CAT_L2: "category",
  C_A_CAT_L3: "aisle",
  BRAND: "brand",
  IMAGES: "images",
  I_IMAGE_FULL: "full",
  I_F_PATH: "path",
};

export async function getStoreId(storeName: string) {
  let retryTimes = 3;
  while (true) {
    try {
      const store = await db
        .select()
        .from(stores)
        .where(eq(stores.name, storeName));
      console.log("Find exist store", store);
      return store[0].id;
    } catch (e) {
      console.log(e);
      retryTimes--;
      if (retryTimes === 0) {
        console.error(e);
        return null;
      }
      console.log(`Store ${storeName} not found, creating a new store...`);
      const storeData: Omit<
        Extract<StoreSchema, { mode: "addStore" }>,
        "mode"
      > = {
        name: storeName,
        imageUrl: marketImages.get(storeName as Supermarket) || "",
      };
      await db.insert(stores).values(storeData).execute();
    }
  }
}

async function getBrandId(brand: string) {
  let retryTimes = 3;
  while (true) {
    try {
      const brandQuery = await db
        .select()
        .from(brands)
        .where(eq(brands.name, brand));
      return brandQuery[0].id;
    } catch (e) {
      if (retryTimes === 0) {
        console.error(e);
        return null;
      }
      retryTimes--;
      console.log(`Brand ${brand} not found, creating a new brand...`);
      const brandData: Omit<
        Extract<BrandSchema, { mode: "addBrand" }>,
        "mode"
      > = {
        name: brand,
      };
      await db.insert(brands).values(brandData);
    }
  }
}

async function getCategoryId(storeId: string | SQLWrapper, category: string) {
  if (!category) {
    return null;
  }
  const categoriesData = await db
    .select()
    .from(categories)
    .where(and(eq(categories.storeId, storeId), eq(categories.name, category)));
  const categoryId = categoriesData.find((b) => b.name === category)?.id;
  if (categoryId) {
    return categoryId;
  }
  return null;
}

async function createCategory(
  storeId: any,
  category: any,
  parentId: string | null
) {
  const categoryData: Omit<
    Extract<CategorySchema, { mode: "addCategory" }>,
    "mode"
  > = {
    name: category,
    parentId: parentId || undefined,
    storeId: storeId,
  };
  await db.insert(categories).values(categoryData);
}

async function getCategoriesId(
  storeId: string | SQLWrapper,
  categoryL1: string,
  categoryL2: string,
  categoryL3: string
) {
  const cat1 = categoryL1 ? await getCategoryId(storeId, categoryL1) : null;
  const cat2 = categoryL2 ? await getCategoryId(storeId, categoryL2) : null;
  const cat3 = categoryL3 ? await getCategoryId(storeId, categoryL3) : null;
  const cats = [cat1, cat2, cat3];
  const catStrings = [categoryL1, categoryL2, categoryL3];
  for (let i = 0; i < 3; i++) {
    if (!cats[i]) {
      if (!catStrings[i]) {
        break;
      }
      await createCategory(storeId, catStrings[i], i > 0 ? cats[i - 1] : null);
      cats[i] = await getCategoryId(storeId, catStrings[i]);
    }
  }
  for (let i = cats.length - 1; i > 0; i--) {
    if (cats[i]) {
      return cats[i];
    }
  }
}

async function getProductId(name, stockCode, barCode, brandId, storeId) {
  const queryResult = await db
    .select()
    .from(products)
    .where(
      and(
        eq(products.name, name),
        eq(products.stockCode, stockCode),
        eq(products.barCode, barCode),
        eq(products.brandId, brandId),
        eq(products.storeId, storeId)
      )
    );
  if (queryResult.length > 0) {
    return queryResult[0].id;
  }
  return null;
}

async function importProductFromWws(products: any) {
  console.log(`importProductFromWws`);
  const storeId = await getStoreId(Supermarket.WWS);
  console.log(`Store ID: ${storeId}`);
  for (const product of products) {
    const productInfo = product[WWS_KEYS.PRODUCT];
    const categories = productInfo[WWS_KEYS.P_CAT];
    const productRawInfo = {
      link: product[WWS_KEYS.PAGE_LINK],
      stockCode: productInfo[WWS_KEYS.P_STOCK_CODE],
      barCode: productInfo[WWS_KEYS.P_BAR_CODE],
      cupPrice: productInfo[WWS_KEYS.P_CUP_PRICE],
      instoreCupPrice: productInfo[WWS_KEYS.P_IN_CUP_PRICE],
      cupMeasure: productInfo[WWS_KEYS.P_CUP_MEASURE],
      price: productInfo[WWS_KEYS.P_PRICE],
      instorePrice: productInfo[WWS_KEYS.P_IN_PRICE],
      name: productInfo[WWS_KEYS.P_NAME],
      displayName: productInfo[WWS_KEYS.P_DISP_NAME],
      slug: productInfo[WWS_KEYS.P_URL_NAME],
      description: productInfo[WWS_KEYS.P_DESC],
      image: productInfo[WWS_KEYS.P_IMAGE],
      unit: productInfo[WWS_KEYS.P_UNIT],
      fullDescription: productInfo[WWS_KEYS.P_DESC_FULL],
      richDescription: productInfo[WWS_KEYS.P_DESC_RICH],
      categoryL1: categories[WWS_KEYS.P_C_CAT_L1],
      categoryL2: categories[WWS_KEYS.P_C_CAT_L2],
      categoryL3: categories[WWS_KEYS.P_C_CAT_L3],
      brand: productInfo[WWS_KEYS.P_BRAND],
      metadata: product,
    };
    await importProductToDb(storeId, productRawInfo);
  }
}

async function importProductFromColes(products: any) {
  console.log(`importProductFromColes`);
  const storeId = await getStoreId(Supermarket.Coles);
  console.log(`Store ID: ${storeId}`);
  for (const product of products) {
    const categories = product[COLES_KEYS.CAT];
    const pricing = product[COLES_KEYS.PRICING];
    const pricingUnit = pricing[COLES_KEYS.P_UNIT];
    const productRawInfo = {
      link: product[COLES_KEYS.PAGE_LINK],
      stockCode: product[COLES_KEYS.STOCK_CODE],
      barCode: product[COLES_KEYS.STOCK_CODE],
      cupPrice: pricingUnit[COLES_KEYS.P_U_CUP_PRICE],
      instoreCupPrice: pricingUnit[COLES_KEYS.P_U_CUP_PRICE],
      cupMeasure:
        pricingUnit[COLES_KEYS.P_U_CUP_MEASURE_QUANT] +
        pricingUnit[COLES_KEYS.P_U_CUP_MEASURE_UNIT],
      price: pricing[COLES_KEYS.P_PRICE],
      instorePrice: pricing[COLES_KEYS.P_PRICE],
      name: product[COLES_KEYS.NAME],
      displayName: product[COLES_KEYS.NAME],
      // slug: productInfo[COLES_KEYS.P_URL_NAME],
      description: product[COLES_KEYS.DESC],
      image: 'https://shop.coles.com.au' + product[COLES_KEYS.IMAGES][0][COLES_KEYS.I_IMAGE_FULL][COLES_KEYS.I_F_PATH],
      unit: pricingUnit[COLES_KEYS.P_U_CUP_MEASURE_UNIT],
      fullDescription: product[COLES_KEYS.DESC_FULL],
      richDescription: product[COLES_KEYS.DESC_FULL],
      categoryL1: categories[0][COLES_KEYS.C_A_CAT_L1],
      categoryL2: categories[0][COLES_KEYS.C_A_CAT_L2],
      categoryL3: categories[0][COLES_KEYS.C_A_CAT_L3],
      brand: product[COLES_KEYS.BRAND],
      metadata: product,
    };
    await importProductToDb(storeId, productRawInfo);
  }
}

async function importProductToDb(storeId, productInfo) {
  const categoryId = await getCategoriesId(
    storeId,
    productInfo.categoryL1,
    productInfo.categoryL2,
    productInfo.categoryL3
  );
  const brandId = await getBrandId(productInfo.brand);
  // Create a new product object
  const productData: Omit<
    Extract<ProductSchema, { mode: "addProduct" }>,
    "mode"
  > = {
    name: productInfo.name,
    nameDisplay: productInfo.displayName,
    stockCode: productInfo.stockCode,
    barCode: productInfo.barCode,
    brandId: brandId,
    categoryId: categoryId,
    storeId: storeId,
    slug: productInfo.slug,
    description: productInfo.description,
    descriptionFull: productInfo.richDescription,
    imageUrl: productInfo.image,
    link: productInfo.link,
    price: productInfo.price,
    priceInStore: productInfo.instorePrice,
    priceCup: productInfo.cupPrice,
    priceCupInStore: productInfo.instoreCupPrice,
    cupMeasure: productInfo.cupMeasure,
    unit: productInfo.unit,
    metadata: productInfo.metadata,
  };
  let productId = await getProductId(
    productInfo.name,
    productInfo.stockCode,
    productInfo.barCode,
    brandId,
    storeId
  );
  if (!productId) {
    await db.insert(products).values(productData);
    productId = await getProductId(
      productInfo.name,
      productInfo.stockCode,
      productInfo.barCode,
      brandId,
      storeId
    );
  }
  const productPriceHistoryData: Omit<
    Extract<ProductPriceHistorySchema, { mode: "addProductPriceHistory" }>,
    "mode"
  > = {
    productId: productId,
    storeId: storeId,
    price: productInfo.price,
    priceInStore: productInfo.instorePrice,
    priceCup: productInfo.cupPrice,
    priceCupInStore: productInfo.instoreCupPrice,
    cupMeasure: productInfo.cupMeasure,
    unit: productInfo.unit,
  };
  await db.insert(productPriceHistory).values(productPriceHistoryData);
}
